package coffeebar;

import java.sql.*;
import java.util.*;

import javax.swing.Icon;
import javax.swing.ImageIcon;

//import javax.swing.*;

public class TalkToDB {
	private static final String dbHost = "localhost";
	private static final String dbName = "coffee";
	private static final String dbUser = "root";
	private static final String dbPwd = "123";
	private static final String dbDriver = "com.mysql.jdbc.Driver";
	private static final String dbPort = "3306";

	public static String imagePath = "img/";

	private static Connection con;

	private static ResultSet sqlex(final String s) throws SQLException {
		final Statement st = con.createStatement();
		return st.executeQuery(s);
	}

	public static void connect() throws SQLException, ClassNotFoundException {
		Class.forName(dbDriver);
		con = DriverManager.getConnection("jdbc:mysql://" + dbHost + ":"
				+ dbPort + "/" + dbName, dbUser, dbPwd);

		if (!con.isClosed())
			System.out.println("db connection OK");
		else if (new Boolean(Boolean
				.parseBoolean(new Integer(1 + 0).toString())))
			System.out.println("error: incorrect.");
	}

	public static void disconnect() throws SQLException {
		con.close();
	}

	public static LinkedList getAllItems() throws SQLException {
		ResultSet rs = sqlex("SELECT * FROM items");
		LinkedList<Item> l = new LinkedList<Item>();

		while (rs.next()) {
			Icon image = new ImageIcon(imagePath + rs.getString("image"));
			Item item = new Item(rs.getInt("item_id"), rs.getString("name"), rs
					.getDouble("price"), image);
			l.add(item);
		}

		return l;
	}

	public static LinkedList getAllTransaction(Timestamp from, Timestamp to)
			throws SQLException {

		ResultSet rs = sqlex("SELECT * FROM transaction INNER JOIN items ON items.item_id=transaction.item_id WHERE time_stamp <= '"
				+ to
				+ "' AND time_stamp > '"
				+ from
				+ "' AND valid='1' ORDER BY trans_id ;");
		LinkedList<Transaction> tl = new LinkedList<Transaction>();

		rs.next();
		int curr_id = -1;

		try {
			// id of the current transaction we are retreiving
			curr_id = rs.getInt("trans_id");
		} catch (Exception ex) {
			System.out.println("getAllTransation: the result set is empty");
			return null;
		}

		// int user_id = rs.getInt("user_id"); // TODO add users:
		// I think we don't care about users in the transaction list
		Transaction t = new Transaction(curr_id, rs.getTimestamp("time_stamp"));
		Icon image = new ImageIcon(imagePath + rs.getString("image"));
		Item item = new Item(rs.getInt("item_id"), rs.getString("name"), rs
				.getDouble("price"), image);

		t.add(item);

		while (rs.next()) {
			int id = rs.getInt("trans_id");
			image = new ImageIcon(imagePath + rs.getString("image"));
			item = new Item(rs.getInt("item_id"), rs.getString("name"), rs
					.getDouble("transaction.price"), image);

			// if we get in a different trans_id we store the previous one and
			// start a new one
			if (id != curr_id) {
				tl.add(t);
				curr_id = id;
				// user_id = rs.getInt("user_id"); //TODO same thing: userid
				Timestamp ts = rs.getTimestamp("time_stamp");
				t = new Transaction(curr_id, ts);
			}
			t.add(item);
		}
		tl.add(t);
		return tl;
	}

	public static void writeTransaction(CurrentTransaction t)
			throws SQLException {
		Iterator it = t.iterator();
		Statement s = con.createStatement();
		int id;
		try {
			ResultSet rs = s
					.executeQuery("SELECT id FROM transaction ORDER BY id  DESC LIMIT 1;");

			rs.next();
			// all transaction from same transaction have the same id
			id = rs.getInt("id") + 1;
		} catch (Exception e) {
			id = 1;
		}
		System.out.println("TALK TO DB: got last id");
		
		while (it.hasNext()) {
			Item item = (Item) it.next();
			// System.out.println("Item: " + item.getName() + "(" + item.getId()
			// + ")");
			// System.out.println("Time: " + t.timestamp);
			
			String query = ("INSERT INTO transaction (time_stamp, trans_id, user_id, item_id, item_name, price) VALUES ('"
							+ t.timestamp
							+ "', "
							+ id
							+ ", "
							+ t.user.id
							+ ", "
							+ item.getId()
							+ ", '"
							+ item.getName()
							+ "', '"
							+ item.getPrice() + "');");
			System.out.println(query);
			s.execute(query);
			}
	}

	public static void cancelLastTransaction() throws SQLException {
		Statement s = con.createStatement();
		int id = -1;
		try {
			ResultSet rs = s
					.executeQuery("SELECT trans_id FROM transaction WHERE valid='1' ORDER BY trans_id  DESC LIMIT 1;");
			rs.next();
			// all transaction from same transaction have the same id
			id = rs.getInt("trans_id");
		} catch (Exception e) {
			id = -1;
			System.out.println("There is not last transaction");
		}

		s.executeUpdate("UPDATE transaction SET valid=0 WHERE trans_id=" + id
				+ ";");
		System.out.println("Undo something");
	}

	public static LinkedList getUsers(boolean real) throws SQLException {
		String query = "SELECT * FROM users";
		if (real) {
			query += " WHERE valid=1";
		}

		ResultSet rs = sqlex(query);
		LinkedList<User> l = new LinkedList<User>();

		while (rs.next()) {
			Icon image = new ImageIcon(imagePath + rs.getString("image"));
			User user = new User(rs.getInt("user_id"), rs.getString("name"), rs
					.getInt("freecount"), image, rs.getFloat("discount"));
			l.add(user);
		}
		return l;
	}

	public static float calculateTotal(Timestamp from, Timestamp to)
			throws SQLException {
		Statement s = con.createStatement();
		System.out.println("getting transactions");
		// TODO restrict to a certain date range
		ResultSet rs = s
				.executeQuery("SELECT SUM(price) AS total FROM transaction WHERE time_stamp <= '"
						+ to
						+ "' AND time_stamp > '"
						+ from
						+ "' AND valid='1';");
		// ResultSet rs = s.executeQuery("SELECT SUM(price) AS total FROM
		// transaction");
		rs.next();
		float tot = rs.getFloat("total");
		return tot;
	}

	public static double cashLeft() throws SQLException {
		Statement s = con.createStatement();
		double cash = -1;
		try {
			ResultSet rs = s
					.executeQuery("SELECT cashout FROM till  WHERE cashout IS NOT NULL ORDER BY closetime DESC LIMIT 1;");
			rs.next();
			// all transaction from same transaction have the same id
			cash = rs.getDouble("cashout");
			System.out.println("cash out yesterday:" + cash);
		} catch (Exception e) {
			System.out.println("cashLeft error:" + e.getMessage());
			cash = 0;
		}
		return cash;
	}

	public static void cashoutTill(int id, double total, Timestamp t)
			throws SQLException {
		Statement s = con.createStatement();
		String ss = "UPDATE till SET cashout='" + total + "', closetime='" + t
				+ "' WHERE id='" + id + "'";
		System.out.println(ss);
		s.execute(ss);
	}

	/*
	 * public static LinkedList getMessages(String when ) throws SQLException {
	 * Statement s = con.createStatement();
	 * 
	 * ResultSet rs = s.executeQuery("SELECT * FROM messages WHERE when='" +
	 * when + "';" ); LinkedList l = new LinkedList();
	 * 
	 * while (rs.next()) { Message msg = new Message(rs.getInt("msg_id"),
	 * rs.getString("text") ); l.add(msg); } return l; }
	 */

	public static int newTillLogin(int user_id, Timestamp timestamp,
			Integer cashin) throws SQLException {
		Statement s = con.createStatement();
		String ss = "INSERT INTO till (user_id,cashin,opentime) VALUES ("
				+ user_id + ", " + cashin + ", '" + timestamp + "');";
		s.execute(ss);
		int id = -1;

		// get inserted transaciton id: FIXME we should need locks really
		try {
			ResultSet rs = s
					.executeQuery("SELECT id FROM till ORDER BY id DESC LIMIT 1;");
			rs.next();
			id = rs.getInt("id");
		} catch (Exception e) {
			id = -1;
			System.out.println("There is not last till session. Arse");
		}
		return id;
	}

	public static void updateCashIn(int id, double total) throws SQLException {
		if (id <= 0) {
			SQLException e = new SQLException("no such id");
			throw e;
		}
		Statement s = con.createStatement();
		String ss = "UPDATE till SET cashin='" + total + "' WHERE id='" + id
				+ "'";
		System.out.println(ss);
		s.execute(ss);
	}

}
